#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
Excel文件和Word文档转换器
将Excel文件转换为CSV和JSON格式，将Word文档转换为文本格式，方便读取和分析
"""

import pandas as pd
import os
import json
from pathlib import Path

# 添加docx模块导入
try:
    import docx
except ImportError:
    print("请安装python-docx模块: pip install python-docx")

def convert_word_to_text(word_file, output_dir=None):
    """
    将Word文档转换为文本格式
    
    Args:
        word_file (str): Word文件路径
        output_dir (str, optional): 输出目录路径，默认与Word文件相同目录
    
    Returns:
        str: 文本文件路径
    """
    try:
        # 检查是否安装了python-docx
        if 'docx' not in globals():
            print("缺少python-docx模块，无法处理Word文档")
            return None
            
        # 获取文件名(不含扩展名)和目录
        file_path = Path(word_file)
        file_name = file_path.stem
        
        if output_dir is None:
            output_dir = file_path.parent
        else:
            output_dir = Path(output_dir)
            
        # 确保输出目录存在
        output_dir.mkdir(parents=True, exist_ok=True)
        
        # 设置输出文件路径
        text_file = output_dir / f"{file_name}.txt"
        
        # 读取Word文档
        print(f"正在读取Word文档: {word_file}")
        doc = docx.Document(word_file)
        
        # 提取文本
        full_text = []
        for para in doc.paragraphs:
            full_text.append(para.text)
            
        # 保存为文本文件
        with open(text_file, 'w', encoding='utf-8') as f:
            f.write('\n'.join(full_text))
        
        print(f"文本文件已保存: {text_file}")
        
        # 打印Word文档的基本信息
        print(f"\n{file_name} 文件信息:")
        print(f"段落数: {len(doc.paragraphs)}")
        print(f"部分内容预览:")
        preview_text = '\n'.join(full_text[:5]) if len(full_text) > 5 else '\n'.join(full_text)
        print(preview_text)
        
        return str(text_file)
    
    except Exception as e:
        print(f"转换 {word_file} 时出错: {e}")
        return None

def convert_excel_to_csv_json(excel_file, output_dir=None):
    """
    将Excel文件转换为CSV和JSON格式
    
    Args:
        excel_file (str): Excel文件路径
        output_dir (str, optional): 输出目录路径，默认与Excel文件相同目录
    
    Returns:
        tuple: 包含CSV路径和JSON路径的元组
    """
    try:
        # 获取文件名(不含扩展名)和目录
        file_path = Path(excel_file)
        file_name = file_path.stem
        
        if output_dir is None:
            output_dir = file_path.parent
        else:
            output_dir = Path(output_dir)
            
        # 确保输出目录存在
        output_dir.mkdir(parents=True, exist_ok=True)
        
        # 设置输出文件路径
        csv_file = output_dir / f"{file_name}.csv"
        json_file = output_dir / f"{file_name}.json"
        
        # 读取Excel文件
        print(f"正在读取Excel文件: {excel_file}")
        
        # 尝试不同的引擎读取Excel文件
        try:
            df = pd.read_excel(excel_file)
        except Exception as e:
            print(f"使用默认引擎读取失败: {e}")
            try:
                df = pd.read_excel(excel_file, engine='openpyxl')
            except Exception as e:
                print(f"使用openpyxl引擎读取失败: {e}")
                df = pd.read_excel(excel_file, engine='xlrd')
        
        # 转换为CSV
        df.to_csv(csv_file, index=False, encoding='utf-8')
        print(f"CSV文件已保存: {csv_file}")
        
        # 转换为JSON
        json_data = df.to_dict(orient='records')
        with open(json_file, 'w', encoding='utf-8') as f:
            json.dump(json_data, f, ensure_ascii=False, indent=2)
        print(f"JSON文件已保存: {json_file}")
        
        # 打印Excel文件的基本信息
        print(f"\n{file_name} 文件信息:")
        print(f"行数: {len(df)}")
        print(f"列数: {len(df.columns)}")
        print("列名:", df.columns.tolist())
        print("数据示例:")
        print(df.head(5))
        
        return str(csv_file), str(json_file)
    
    except Exception as e:
        print(f"转换 {excel_file} 时出错: {e}")
        return None, None

def process_all_files(directory):
    """
    处理指定目录下的所有Excel和Word文件
    
    Args:
        directory (str): 目录路径
    """
    directory = Path(directory)
    excel_files = list(directory.glob("*.xls")) + list(directory.glob("*.xlsx"))
    word_files = list(directory.glob("*.docx")) + list(directory.glob("*.doc"))
    
    if not excel_files and not word_files:
        print(f"在 {directory} 中没有找到Excel或Word文件")
        return
    
    if excel_files:
        print(f"发现 {len(excel_files)} 个Excel文件:")
        for file in excel_files:
            print(f"\n处理文件: {file.name}")
            convert_excel_to_csv_json(file)
    
    if word_files:
        print(f"\n发现 {len(word_files)} 个Word文件:")
        for file in word_files:
            print(f"\n处理文件: {file.name}")
            convert_word_to_text(file)

def analyze_excel_structure(excel_files):
    """
    分析多个Excel文件的结构并生成数据库设计建议
    
    Args:
        excel_files (list): Excel文件路径列表
    """
    tables_info = {}
    
    for file in excel_files:
        try:
            file_path = Path(file)
            table_name = file_path.stem
            
            # 读取Excel文件
            try:
                df = pd.read_excel(file)
            except Exception:
                try:
                    df = pd.read_excel(file, engine='openpyxl')
                except Exception:
                    df = pd.read_excel(file, engine='xlrd')
            
            # 分析列的数据类型
            columns_info = []
            for col_name in df.columns:
                # 获取非空值
                non_null_values = df[col_name].dropna()
                data_type = "VARCHAR(255)"  # 默认数据类型
                
                if len(non_null_values) > 0:
                    # 根据列的数据样本确定可能的数据类型
                    if pd.api.types.is_numeric_dtype(non_null_values):
                        if pd.api.types.is_integer_dtype(non_null_values):
                            data_type = "INT"
                        else:
                            data_type = "DECIMAL(10,2)"
                    elif pd.api.types.is_datetime64_dtype(non_null_values):
                        data_type = "DATETIME"
                    else:
                        # 如果是字符串，根据最长值确定长度
                        if len(non_null_values) > 0:
                            max_len = non_null_values.astype(str).str.len().max()
                            if max_len > 255:
                                data_type = "TEXT"
                            else:
                                data_type = f"VARCHAR({max_len + 10})"
                
                # 检查是否可能是主键
                is_unique = len(non_null_values.unique()) == len(non_null_values) and len(non_null_values) > 0
                
                columns_info.append({
                    "name": col_name,
                    "data_type": data_type,
                    "nullable": df[col_name].isnull().any(),
                    "possible_primary_key": is_unique and not df[col_name].isnull().any()
                })
            
            tables_info[table_name] = {
                "columns": columns_info,
                "row_count": len(df)
            }
            
        except Exception as e:
            print(f"分析 {file} 时出错: {e}")
    
    # 生成数据库设计建议
    output_file = Path('数据库设计建议.md')
    
    with open(output_file, 'w', encoding='utf-8') as f:
        f.write("# 数据库设计建议\n\n")
        f.write("基于Excel文件的自动分析结果\n\n")
        
        for table_name, table_info in tables_info.items():
            f.write(f"## {table_name} 表\n\n")
            f.write(f"记录数: {table_info['row_count']}\n\n")
            
            f.write("```sql\nCREATE TABLE `" + table_name + "` (\n")
            
            # 寻找可能的主键
            primary_key_cols = [col["name"] for col in table_info["columns"] if col["possible_primary_key"]]
            
            # 生成列定义
            for i, col in enumerate(table_info["columns"]):
                nullable = "" if col["nullable"] else " NOT NULL"
                comment = f" COMMENT '{col['name']}'"
                
                f.write(f"    `{col['name']}` {col['data_type']}{nullable}{comment}")
                
                if i < len(table_info["columns"]) - 1 or primary_key_cols:
                    f.write(",")
                f.write("\n")
            
            # 添加主键
            if primary_key_cols:
                primary_keys_str = ", ".join([f"`{col}`" for col in primary_key_cols])
                f.write(f"    PRIMARY KEY ({primary_keys_str})\n")
            
            f.write(") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='{table_name}';\n```\n\n")
    
    print(f"\n数据库设计建议已保存到: {output_file}")
            

if __name__ == "__main__":
    # 当前脚本所在目录
    current_dir = Path(__file__).parent
    
    # 处理当前目录下的所有Excel和Word文件
    process_all_files(current_dir)
    
    # 分析Excel文件结构并生成数据库设计建议
    excel_files = list(current_dir.glob("*.xls")) + list(current_dir.glob("*.xlsx"))
    if excel_files:
        print("\n\n开始分析Excel文件结构...")
        analyze_excel_structure(excel_files)
        
    print("\n处理完成!")